const { query, db } = require('../database/db')

// 检查内务的模型
class InspectModel {
    // 内务检查记录添加
    static add(inspect_num, type, class_id, inspect_date, inspecter, score, del_score, comment, project) {
        let sql = `
            INSERT INTO inspects (inspect_num,type,class_id,inspect_date,inspecter,score,del_score,comment,project) 
                VALUES (?,?,?,?,?,?,?,?,?)
        `
        return query(sql, [inspect_num, type, class_id, inspect_date, inspecter, score, del_score, comment, project])
    }
    // 内务检查记录软删除
    static del_f(id) {
        let sql = `
            UPDATE inspects SET state_del = 1 WHERE id = ?
        `
        return query(sql, [id])
    }
    // 内务检查记录真删除
    static del_t(id) {
        let sql = `
            DELETE FROM inspects WHERE id = ?
        `
        return query(sql, [id])
    }
    // 内务检查记录获取数据
    static get(id) {
        let sql = `
            SELECT inspects.inspect_num, inspects.type, inspects.inspect_date, inspects.inspecter, inspects.score,
                inspects.del_score, inspects.comment, inspects.project, classes.name AS class_name,
                programs.name AS program_name
            FROM inspects
            JOIN classes ON inspects.class_id = classes.id
            JOIN programs ON classes.program_id = programs.id
            WHERE inspects.id = ? AND inspects.state_del = 0;
        `
        return query(sql, [id])
    }
    // 内务检查记录设置数据
    static set(inspect_num, type, class_id, inspect_date, inspecter, score, del_score, comment, project, id) {
        let sql = `
            UPDATE inspects SET inspect_num=?, type=?, class_id=?, inspect_date=?, inspecter=?,
             score=?, del_score=?, comment=?, project=?
            WHERE id = ?
        `
        return query(sql, [inspect_num, type, class_id, inspect_date, inspecter, score, del_score, comment, project, id])
    }
    // 内务检查记录列表搜索
    static async list(inspect_num, type, class_id, program_id, inspect_date, page, page_size) {
        // 基础SQL，不包含WHERE和分页
        let sql_base = `
            SELECT 
                inspects.id,
                inspects.inspect_num,
                inspects.type,
                inspects.inspect_date,
                inspects.inspecter,
                inspects.score,
                inspects.del_score,
                inspects.comment,
                inspects.project,
                classes.name AS class_name,
                classes.id AS class_id,
                programs.name AS program_name
            FROM inspects
            JOIN classes ON inspects.class_id = classes.id
            JOIN programs ON classes.program_id = programs.id
        `;
    
        // 初始化条件和条件参数数组
        let conditions = [`inspects.state_del = 0`];
        let conditionsParams = [];
    
        // 根据参数添加条件
        if (inspect_num) {
            conditions.push(`inspects.inspect_num = ?`);
            conditionsParams.push(inspect_num);
        }
        if (type) {
            conditions.push(`inspects.type = ?`);
            conditionsParams.push(type);
        }
        if (class_id) {
            conditions.push(`inspects.class_id = ?`);
            conditionsParams.push(class_id);
        }
        if (program_id) {
            conditions.push(`classes.program_id = ?`);
            conditionsParams.push(program_id);
        }
        if (inspect_date) {
            conditions.push(`inspects.inspect_date = ?`);
            conditionsParams.push(inspect_date);
        }
    
        // 构建WHERE子句
        if (conditions.length > 0) {
            sql_base += ` WHERE ${conditions.join(' AND ')}`;
        }
    
        // 添加分页
        const offset = (page - 1) * page_size;
        sql_base += ` LIMIT ? OFFSET ?`;
        const params = [...conditionsParams, page_size, offset];
    
        // 构建countSql
        let countSql = `
            SELECT COUNT(*) as count 
            FROM inspects
            JOIN classes ON inspects.class_id = classes.id
            JOIN programs ON classes.program_id = programs.id
        `;
        if (conditions.length > 0) {
            countSql += ` WHERE ${conditions.join(' AND ')}`;
        }
    
        // 执行查询
        const [inspectsResult, countResult] = await Promise.all([
            query(sql_base, params),
            query(countSql, conditionsParams)
        ]);
    
        return {
            inspects: inspectsResult,
            total: countResult[0]?.count || 0
        };
    }
    // 获取所有房间号
    static allRoomId(){
        let sql = `
            SELECT hostels.room_id FROM hostels
        `
        return query(sql)
    }
}

module.exports = InspectModel